﻿using System;
using System.IO;
using System.Threading.Tasks;
using Windows.Storage;
using com.IronOne.BoardPACWinAppDAO.DB;
using com.IronOne.BoardPACWinAppUtil.Enums;
using com.IronOne.BoardPACWinAppUtil.Util;
using SQLite;
using Version = com.IronOne.BoardPACWinAppDAO.DB.Version;

namespace com.IronOne.BoardPACWinAppDAO.DbOperations
{
    public class DbOperations : IDbOperations
    {
        public static string GeneralDbPath;
        public static string UserDbPath;
        private SQLiteAsyncConnection _connectionGenAsync;
        private SQLiteConnection _connectionGenSync;
        private SQLiteAsyncConnection _connectionUserAsync;
        //private SQLiteConnection _connectionUserSync;

        public DbOperations()
        {
            GeneralDbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "General.sqlite");
            if (Global.UserId != 0)
                UserDbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, Global.UserId.ToString(),
                    Global.UserId + ".sqlite");
        }

        public bool CreateGeneralDatabase()
        {
            try
            {
                using (_connectionGenSync = GetSyncConnection(DbConnectionType.GeneralDbConnection))
                {
                    _connectionGenSync.CreateTable<AppSettings>();
                    _connectionGenSync.CreateTable<UserMapper>();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public async Task<bool> CreateUserDatabase(int userId)
        {
            try
            {
                //Create userwise folder inside local folder
                var userFolder =
                    await
                        ApplicationData.Current.LocalFolder.CreateFolderAsync(userId.ToString(),
                            CreationCollisionOption.OpenIfExists);
                UserDbPath = Path.Combine(userFolder.Path, userId + ".sqlite");

                _connectionUserAsync = new SQLiteAsyncConnection(UserDbPath);

                /*Enable Write Ahead Logging*/
                var conn = new SQLiteConnection(UserDbPath);
                var sqlCommand = new SQLiteCommand(conn);
                //Enable Write Ahead Logging
                sqlCommand.CommandText = "PRAGMA journal_mode = WAL;";
                var rslt = sqlCommand.ExecuteNonQuery();

                var tableResult = await _connectionUserAsync.CreateTablesAsync(
                    typeof (AnnotationShareByUser),
                    typeof (AnnotationShareWithUser),
                    typeof (DB.Comments),
                    typeof (Heading),
                    typeof (Log),
                    typeof (MainCategory),
                    typeof (DB.Meeting),
                    typeof (Member),
                    typeof (Paper),
                    typeof (Settings),
                    typeof(SettingsUsage),
                    typeof (SubCategory),
                    typeof (User),
                    typeof (Version),
                    typeof(AnnotationRestore)
                    );

                var sqlCommandTrigger = new SQLiteCommand(conn)
                {
                    CommandText = "CREATE TRIGGER \"update_version\"" +
                                  " BEFORE UPDATE OF VersionId ON Paper WHEN new.VersionId != old.VersionId" +
                                  " BEGIN INSERT INTO Version (\"PaperId\",\"VersionId\",\"CreatedDate\",\"VersionInfo\") VALUES (new.paperId, new.VersionId,new.LastUpadatedDateTime,new.VersionInfo); END"
                };
                var rslt1 = sqlCommandTrigger.ExecuteNonQuery();

                sqlCommandTrigger.CommandText = "CREATE TRIGGER \"update_version_on_paper_insert\"" +
                                                " BEFORE INSERT ON Paper" +
                                                " FOR EACH ROW" +
                                                " WHEN 0 = (Select count(VersionId) from Version where VersionId=new.VersionId)" +
                                                " BEGIN INSERT INTO Version (\"PaperId\",\"VersionId\",\"CreatedDate\",\"VersionInfo\") VALUES (new.PaperId, new.VersionId,new.LastUpadatedDateTime,new.VersionInfo); END";
                rslt1 = sqlCommandTrigger.ExecuteNonQuery();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public async Task<bool> IsGeneralDatabaseExists()
        {
            try
            {
                var sf = await ApplicationData.Current.LocalFolder.GetFileAsync("General.sqlite");
                return sf != null;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public async Task<bool> IsUserDatabaseExists(string userId)
        {
            try
            {
                var dbFolder = await ApplicationData.Current.LocalFolder.GetFolderAsync(userId);
                if (dbFolder != null)
                {
                    var sf = await dbFolder.GetFileAsync(userId + ".sqlite");
                    if (sf != null)
                    {
                        UserDbPath = Path.Combine(dbFolder.Path, userId + ".sqlite");
                        _connectionUserAsync = new SQLiteAsyncConnection(UserDbPath);
                        return true;
                    }
                }
            }
            catch (Exception)
            {
            }
            return false;
        }

        public SQLiteAsyncConnection GetAsyncConnection(DbConnectionType connectionType)
        {
            try
            {
                switch (connectionType)
                {
                    case DbConnectionType.GeneralDbConnection:
                        if (_connectionGenAsync == null)
                            return _connectionGenAsync = new SQLiteAsyncConnection(GeneralDbPath);
                        return _connectionGenAsync;

                    case DbConnectionType.UserDbConnetion:
                        var userDbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, Global.UserId.ToString(),
                            Global.UserId + ".sqlite");

                        //if (ConnectionUserAsync == null)
                        _connectionUserAsync = null;
                        return _connectionUserAsync = new SQLiteAsyncConnection(userDbPath);
                    //return ConnectionUserAsync;

                    default:
                        return null;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public SQLiteConnection GetSyncConnection(DbConnectionType connectionType)
        {
            try
            {
                switch (connectionType)
                {
                    /*case DbConnectionType.GeneralDbConnection:
                        if (ConnectionGenSync != null) { ConnectionGenSync.Close(); ConnectionGenSync.Dispose(); }

                        ConnectionGenSync = new SQLite.SQLiteConnection(GeneralDbPath);
                        return ConnectionGenSync;

                    case DbConnectionType.UserDbConnetion:
                        if (ConnectionUserSync != null) { ConnectionUserSync.Close(); ConnectionUserSync.Dispose(); }

                        ConnectionUserSync = new SQLite.SQLiteConnection(UserDbPath);
                        return ConnectionUserSync;*/


                    case DbConnectionType.GeneralDbConnection:
                        if (_connectionGenSync == null)
                            return _connectionGenSync = new SQLiteConnection(GeneralDbPath);
                        return _connectionGenSync;

                    case DbConnectionType.UserDbConnetion:
                        //if (ConnectionUserSync == null)
                        //_connectionUserSync = null;
                        return new SQLiteConnection(UserDbPath);
                    //return ConnectionUserSync;

                    default:
                        return null;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<SQLiteAsyncConnection> GetAsyncConnectionOffline(DbConnectionType connectionType, int? userId)
        {
            try
            {
                switch (connectionType)
                {
                    case DbConnectionType.GeneralDbConnection:
                        _connectionGenAsync = null;
                        _connectionGenAsync = new SQLiteAsyncConnection(GeneralDbPath);
                        return _connectionGenAsync;

                    case DbConnectionType.UserDbConnetion:
                    {
                        if (userId == null) return null;

                        var dbFolder = await ApplicationData.Current.LocalFolder.GetFolderAsync(userId.ToString());
                        if (dbFolder == null) return null;

                        var sf = await dbFolder.GetFileAsync((userId + ".sqlite"));
                        if (sf == null) return null;

                        UserDbPath = Path.Combine(dbFolder.Path, userId + ".sqlite");
                        _connectionUserAsync = null;
                        _connectionUserAsync = new SQLiteAsyncConnection(UserDbPath);
                        return _connectionUserAsync;
                    }

                    default:
                        return null;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool CloseAllConnections()
        {
            try
            {
                _connectionUserAsync = null;
                //_connectionUserSync = null;

                _connectionGenAsync = null;
                _connectionGenSync = null;

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}